Course Overview

Relational Database Design and Modeling for Software Engineers

Rate this course

Ask Author

Contribute

Back to course home

0% completed

Vote For New Content

Introduction

Course Overview

Before the Advent of Database

Fundamental Concepts

What is a Database?

Database management systems (DBMS)

Overview of Relational Databases

Relational Vs. Non-Relational Databases

Data Modeling

Introduction to Data Modeling

Levels of Data Modeling

Data Modeling Process

Best Practices in Data Modeling

Quiz

Entity-Relationship (ER) Data Modeling

Introduction to ER Models

Entities, Attributes, and Relationships

Cardinality and Participation

Attributes of Relationship Types

Weak Entity Types

Creating an ER Diagram for Employee Management System

Quiz

The Relational Data Model

Introduction to the Relational Model

Relations, Tuples, and Attributes

Keys in Relational Databases

Relational Integrity Constraints

Converting ER Diagrams to Relational Model

Best Practices for ER Diagrams to Relational Models

Quiz

Functional Dependency

Introduction to Functional Dependency

Types of Functional Dependencies

Inference Rules for Functional Dependencies

Closure of Attribute Sets

Quiz

Normalization

Introduction to Normalization

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce-Codd Normal Form (BCNF)

Higher Normal Forms (4NF, 5NF)

Exercise 1

Solution to Exercise 1

Exercise 2

Solution to Exercise 2

Case Studies

Designing a Hospital Management System

Designing a Hotel Management System

Designing a Bank Management System

Designing an Instagram

Designing an E-commerce Platform

Designing an Online Food Delivery System

Exercise 2

Problem Statement

A company maintains the following table to track employee information, which is already in Second Normal Form (2NF):

Emp_ID	Emp_Name	DOB	Area	City	State	Zip
101	Alice	1990-05-01	Downtown	New York	NY	10001
102	Bob	1988-08-12	Midtown	New York	NY	10002
103	Charlie	1992-11-23	Central	Los Angeles	CA	90001
104	David	1985-03-15	West End	Chicago	IL	60601

Task:

Analyze whether the given table is in Third Normal Form (3NF).
If the table is not in 3NF, convert it to 3NF by eliminating transitive dependencies.

Hint:

Identify the primary key of the table.
Determine if table is in the 2NF or not.
Check for transitive dependencies:
- Attributes like Area, City, and State depend on Zip rather than directly on Emp_ID.
Remove transitive dependencies by splitting the table into smaller tables.

In the next lesson, we will provide the solution to this problem.

Solution to Exercise 1

Solution to Exercise 2

Mark as Completed

On this page

Problem Statement

Task:

Hint: